package com.wsc.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.wsc.entity.ConsumeLog;

public interface ConsumeLogRepository extends JpaRepository<ConsumeLog, Integer>, JpaSpecificationExecutor<ConsumeLog> {

	/**
	 * varchar 转 Date(字段，时间格式) str_to_date(intime, '%Y-%m-%d') IF (:status is not
	 * null, c.status = :status , 1 = 1) 获取所有人员每个阅览室使用情况，生成图表数据
	 */
	@Query(value = "select r.name,count(r.name) from t_consumelogs c inner join t_readrooms r on c.readroomid = r.id where IF(:intime is not null,str_to_date(c.intime,'%Y-%m-%d') >= :intime,1 = 1) and IF(:outtime is not null,str_to_date(c.outtime,'%Y-%m-%d') <= :outtime,1 = 1) and IF(:status is not null, c.status = :status , 1 = 1) group by c.readroomid", nativeQuery = true)
	List<Object[]> getChartData(@Param("intime") Date intime, @Param("outtime") Date outtime,
			@Param("status") Integer status);

	/***
	 * 
	 * 根据时间、卡号、阅览室编号查询记录 varchar 转 Date(字段，时间格式) str_to_date(intime, '%Y-%m-%d')
	 * 
	 */

	/***
	 * 查询结果不为null，即为第二次刷卡（即为刷出）
	 * 
	 * @param cardno
	 * @param readroomid
	 * @return
	 */
	@Query(nativeQuery = true, value = "select * from t_consumelogs where cardno = :cardno and readroomid = :readroomid and status = 1")
	ConsumeLog getOneByCardnoReadroomid(@Param("cardno") String cardno, @Param("readroomid") Integer readroomid);

	/***
	 * 修改status为0
	 */
	@Modifying
	@Query(nativeQuery = true, value = "update t_consumelogs set status = 0")
	void updateAllStatusToZero();

	/***
	 * 获取当天每个阅览室使用人次
	 * 
	 * @param today
	 * @return
	 */
	@Query(nativeQuery = true, value = "select readroomid,count(*) from t_consumelogs where str_to_date(intime, '%Y-%m-%d') = :today group by readroomid")
	List<Object[]> getAllByTodayGroupByReadroomid(@Param("today") String today);

	/***
	 * 获取所有教师每个阅览室使用情况，生成图表数据
	 */
	@Query(value = "select r.name,count(r.name) from t_consumelogs c inner join t_readrooms r on c.readroomid = r.id inner join t_teachers t on c.cardno = t.cardno where IF(:intime is not null,str_to_date(c.intime,'%Y-%m-%d') >= :intime,1 = 1) and IF(:outtime is not null,str_to_date(c.outtime,'%Y-%m-%d') <= :outtime,1 = 1) and IF(:status is not null, c.status = :status , 1 = 1) group by c.readroomid", nativeQuery = true)
	List<Object[]> getChartDataAboutTeacher(@Param("intime") Date intime, @Param("outtime") Date outtime,
			@Param("status") Integer status);

	/***
	 * 获取所有学生每个阅览室使用情况，生成图表数据
	 */
	@Query(value = "select r.name,count(r.name) from t_consumelogs c inner join t_readrooms r on c.readroomid = r.id inner join t_students s on c.cardno = s.cardno where IF(:intime is not null,str_to_date(c.intime,'%Y-%m-%d') >= :intime,1 = 1) and IF(:outtime is not null,str_to_date(c.outtime,'%Y-%m-%d') <= :outtime,1 = 1) and IF(:status is not null, c.status = :status , 1 = 1) group by c.readroomid", nativeQuery = true)
	List<Object[]> getChartDataAboutStudent(@Param("intime") Date intime, @Param("outtime") Date outtime,
			@Param("status") Integer status);
	
}
